package property

import (
	"fmt"
	"iot-base/common/dbconn"
	"iot-base/common/logger"
	"strconv"
	"time"
)

//FindWeekTimeByTime 获取本周的星期一，和星期日的 时间
func FindWeekTimeByTime(datetime time.Time) (startDay, endDay time.Time) {
	weekday := datetime.Weekday()
	if weekday > 0 {
		startDay = datetime.Add(time.Hour * 24 * time.Duration(1-weekday)) //sunday 是0
		endDay = datetime.Add(time.Hour * 24 * time.Duration(7-weekday))
	} else {
		startDay = datetime //sunday 是0
		endDay = datetime.Add(time.Hour * 24 * time.Duration(6))
	}

	return
}

// DropTable 删除表(只能内部调用)
func DropTable(isAddYear bool, tableName string, date time.Time) {
	//startTime, _ := FindWeekTimeByTime(date) // 获取周一的时间
	fmt.Println(date)
	_, week := date.ISOWeek()
	if isAddYear == true {
		tableName = fmt.Sprintf("%v_%v_%v", tableName, date.Year(), week)
	} else {
		tableName = tableName + "_" + strconv.Itoa(week)
	}

	sql := "DROP TABLE IF EXISTS " + tableName
	dbconn.DBConnection.Exec(sql)
}

func RealDataSplit(dataTime time.Time) {
	startTime, endTime := FindWeekTimeByTime(dataTime)
	startStr := startTime.Format("2006-01-02")
	endStr := endTime.AddDate(0, 0, 1).Format("2006-01-02")
	check := fmt.Sprintf("check( pick_time >= DATE '%s' AND pick_time < DATE '%s' )", startStr, endStr)
	_, week := dataTime.ISOWeek()
	tableName := "realdata_" + strconv.Itoa(week)
	// 创建分区表
	result := dbconn.DBConnection.Raw("select count(*) as number from pg_class where relname = '" + tableName + "';")
	var num int64
	result.Scan(&num)
	if num <= 0 {
		// 创建分区表表
		dbconn.DBConnection.Exec(`create table ` + tableName + "(" + check + ")INHERITS (realdata);")
		// 创建索引
		dbconn.DBConnection.Exec("CREATE UNIQUE INDEX if not exists " + tableName + "_index ON " + tableName + "(tid,pid,sid,mindex,pick_time)")
		// 创建函数，挂载磁盘
		dbconn.DBConnection.Exec(`CREATE OR REPLACE FUNCTION realdata_partition_trigger()
RETURNS TRIGGER AS $$
DECLARE insert_statement TEXT;
DECLARE start_time_text TEXT;
BEGIN
	SELECT date_part('week', NEW.pick_time) INTO start_time_text;
	insert_statement := 'INSERT INTO realdata_'
		|| start_time_text
		||' VALUES ($1.*)';
	EXECUTE insert_statement USING NEW;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_realdata_partition_trigger
BEFORE INSERT ON realdata
FOR EACH ROW EXECUTE PROCEDURE realdata_partition_trigger();`)
	}
}

func AlarmStateSplit(dataTime time.Time) {
	startTime, endTime := FindWeekTimeByTime(dataTime)
	startStr := startTime.Format("2006-01-02")
	endStr := endTime.AddDate(0, 0, 1).Format("2006-01-02")
	check := fmt.Sprintf("check( time_changed >= DATE '%s' AND time_changed < DATE '%s' )", startStr, endStr)
	_, week := dataTime.ISOWeek()
	tableName := "alarm_states_" + strconv.Itoa(week)
	// 创建分区表
	result := dbconn.DBConnection.Raw("select count(*) as number from pg_class where relname = '" + tableName + "';")
	var num int64
	result.Scan(&num)
	if num <= 0 {
		// 创建分区表表
		dbconn.DBConnection.Exec(`create table ` + tableName + "(" + check + ") INHERITS (alarm_states);")
		var seq int64
		dbconn.DBConnection.Raw("select count(*) as number from pg_class where relname = 'alarm_states_uid_seq';").Scan(&seq)
		if seq == 0 {
			dbconn.DBConnection.Exec("CREATE SEQUENCE alarm_states_uid_seq INCREMENT BY 1 MINVALUE 1 START WITH 2 NO CYCLE;")
		}
		// 设置自增主键
		dbconn.DBConnection.Exec("alter table " + tableName + " alter column uid set default nextval('alarm_states_uid_seq');")
		// 设置索引
		dbconn.DBConnection.Exec("CREATE INDEX " + tableName + "_id_index ON " + tableName + " (uid);")
		//dbconn.DBConnection.Exec("CREATE INDEX " + tableName + "_station_id_hash ON " + tableName + " USING hash (station_id);")
		// 创建函数，挂载磁盘
		dbconn.DBConnection.Exec(`CREATE OR REPLACE FUNCTION alarm_states_partition_trigger()
RETURNS TRIGGER AS $$
DECLARE insert_statement TEXT;
DECLARE start_time_text TEXT;
BEGIN
	SELECT date_part('week', NEW.time_changed) INTO start_time_text;
	insert_statement := 'INSERT INTO alarm_states_'
		|| start_time_text
		||' VALUES ($1.*)';
	EXECUTE insert_statement USING NEW;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_alarm_states_partition_trigger
BEFORE INSERT ON alarm_states
FOR EACH ROW EXECUTE PROCEDURE alarm_states_partition_trigger();`)
	}
}

func AlarmHistroySplit(dataTime time.Time) {
	startTime, endTime := FindWeekTimeByTime(dataTime)
	startStr := startTime.Format("2006-01-02")
	endStr := endTime.AddDate(0, 0, 1).Format("2006-01-02")
	check := fmt.Sprintf("check( clear_time >= DATE '%s' AND clear_time < DATE '%s' )", startStr, endStr)
	_, week := dataTime.ISOWeek()
	tableName := fmt.Sprintf("alarm_histroys_%v_%v", time.Now().Year(), week)
	logger.SLog.Info("create table ", tableName)
	//tableName := "alarm_histroys_" + strconv.Itoa(week)
	// 创建分区表
	err := dbconn.DBConnection.Exec(`CREATE OR REPLACE FUNCTION alarm_histroys_partition_trigger()
RETURNS TRIGGER AS $$
DECLARE insert_statement TEXT;
DECLARE start_time_text TEXT;
DECLARE name_year TEXT;
BEGIN
	SELECT date_part('week', NEW.clear_time) INTO start_time_text;
	SELECT date_part('year', NEW.clear_time) INTO name_year;
	insert_statement := 'INSERT INTO alarm_histroys_'
		|| name_year
		|| '_'
		|| start_time_text
		||' VALUES ($1.*)';
	EXECUTE insert_statement USING NEW;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;`).Error
	logger.SLog.Info("create db procedure ", err)
	err = dbconn.DBConnection.Exec(`CREATE TRIGGER insert_alarm_histroys_partition_trigger
	BEFORE INSERT ON alarm_histroys
	FOR EACH ROW EXECUTE PROCEDURE alarm_histroys_partition_trigger();`).Error
	logger.SLog.Info("create db TRIGGER ", err)
	result := dbconn.DBConnection.Raw("select count(*) as number from pg_class where relname = '" + tableName + "';")
	var num int64
	result.Scan(&num)
	if num <= 0 {
		// 创建分区表表
		dbconn.DBConnection.Exec(`create table ` + tableName + "(" + check + ") INHERITS (alarm_histroys);")
		var seq int64
		dbconn.DBConnection.Raw("select count(*) as number from pg_class where relname = 'alarm_histroys_uid_seq';").Scan(&seq)
		if seq == 0 {
			dbconn.DBConnection.Exec("CREATE SEQUENCE alarm_histroys_uid_seq INCREMENT BY 1 MINVALUE 1 START WITH 2 NO CYCLE;")
		}
		// 设置自增主键
		dbconn.DBConnection.Exec("alter table " + tableName + " alter column uid set default nextval('alarm_histroys_uid_seq');")
		// 设置索引
		dbconn.DBConnection.Exec("CREATE INDEX " + tableName + "_id_index ON " + tableName + " (uid);")
		//dbconn.DBConnection.Exec("CREATE INDEX " + tableName + "_station_id_hash ON " + tableName + " USING hash (station_id);")
		// 创建函数，挂载磁盘

	}
}

func HisDataSplit(dataTime time.Time) {
	startTime, endTime := FindWeekTimeByTime(dataTime)
	startStr := startTime.Format("2006-01-02")
	endStr := endTime.AddDate(0, 0, 1).Format("2006-01-02")
	check := fmt.Sprintf("check( pick_time >= DATE '%s' AND pick_time < DATE '%s' )", startStr, endStr)
	_, week := dataTime.ISOWeek()
	tableName := "his_data_" + strconv.Itoa(week)
	// 创建分区表
	result := dbconn.DBConnection.Raw("select count(*) as number from pg_class where relname = '" + tableName + "';")
	var num int64
	result.Scan(&num)
	if num <= 0 {
		// 创建分区表表
		dbconn.DBConnection.Exec(`create table ` + tableName + "(" + check + ") INHERITS (his_data);")
		// 设置索引
		dbconn.DBConnection.Exec("CREATE INDEX " + tableName + "_pick_time_index ON " + tableName + " (pick_time);")
		//dbconn.DBConnection.Exec("CREATE INDEX " + tableName + "_station_id_hash ON " + tableName + " USING hash (station_id);")
		// 创建函数，挂载磁盘
		dbconn.DBConnection.Exec(`CREATE OR REPLACE FUNCTION his_data_partition_trigger()
RETURNS TRIGGER AS $$
DECLARE insert_statement TEXT;
DECLARE start_time_text TEXT;
BEGIN
	SELECT date_part('week', NEW.pick_time) INTO start_time_text;
	insert_statement := 'INSERT INTO his_data_'
		|| start_time_text
		||' VALUES ($1.*)';
	EXECUTE insert_statement USING NEW;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_his_data_partition_trigger
BEFORE INSERT ON  his_data
FOR EACH ROW EXECUTE PROCEDURE his_data_partition_trigger();`)
	}
}

func ReDataSplit(dataTime time.Time) {
	startTime, endTime := FindWeekTimeByTime(dataTime)
	startStr := startTime.Format("2006-01-02")
	endStr := endTime.AddDate(0, 0, 1).Format("2006-01-02")
	check := fmt.Sprintf("check( pick_time >= DATE '%s' AND pick_time < DATE '%s' )", startStr, endStr)
	_, week := dataTime.ISOWeek()
	tableName := "re_data_" + strconv.Itoa(week)
	// 创建分区表
	result := dbconn.DBConnection.Raw("select count(*) as number from pg_class where relname = '" + tableName + "';")
	var num int64
	result.Scan(&num)
	if num <= 0 {
		// 创建分区表表
		dbconn.DBConnection.Exec(`create table ` + tableName + "(" + check + ") INHERITS (re_data);")
		// 设置索引
		dbconn.DBConnection.Exec("CREATE INDEX " + tableName + "_pick_time_index ON " + tableName + " (pick_time);")
		//dbconn.DBConnection.Exec("CREATE INDEX " + tableName + "_station_id_hash ON " + tableName + " USING hash (station_id);")
		// 创建函数，挂载磁盘
		dbconn.DBConnection.Exec(`CREATE OR REPLACE FUNCTION re_data_partition_trigger()
RETURNS TRIGGER AS $$
DECLARE insert_statement TEXT;
DECLARE start_time_text TEXT;
BEGIN
	SELECT date_part('week', NEW.pick_time) INTO start_time_text;
	insert_statement := 'INSERT INTO re_data_'
		|| start_time_text
		||' VALUES ($1.*)';
	EXECUTE insert_statement USING NEW;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_re_data_partition_trigger
BEFORE INSERT ON  re_data
FOR EACH ROW EXECUTE PROCEDURE re_data_partition_trigger();`)
	}
}
